/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DKTC_WS;

import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import ConnectDB.connectData;
//import DTO.chitiet_pdk;
import DTO.chitiet_phong;
import DTO.giangvien;
import DTO.hocky;
import DTO.lophoc;
import DTO.monhoc;
import DTO.montienquyet;
import DTO.namhoc;
import DTO.phieudangky;
import DTO.phong;
import DTO.quantri;
import DTO.sinhvien;
import DTO.thamso;
import DTO.thongtindk;
import DTO.thongtinnamhochientai;
import DTO.tintuc;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
/**
 *
 * @author THIENSUHACK
 */
@WebService(serviceName = "DKTC_WebService")
public class DKTC_WebService {
connectData connect=new connectData();
    /** This is a sample web service operation */
    @WebMethod(operationName = "hello")
    public String hello(@WebParam(name = "name") String txt) {
        return "Hello " + txt + " !";
    }
    @WebMethod(operationName = "kiemtraketnoi")
    public String testconnect() {
        return connect.GetDataDB();
    }
    // phần sinh viên 
       @WebMethod(operationName = "ThemSinhVien")
    public int ThemSinhVien(@WebParam(name = "MASV") String masv,@WebParam(name = "HOTEN") String hoten,
    @WebParam(name = "GIOITINH") short gioitinh,@WebParam(name = "NGAYSINH") String ngaysinh,
    @WebParam(name = "DIACHI") String diachi,@WebParam(name = "EMAIL") String email,
    @WebParam(name = "PHONE") String phone,@WebParam(name = "MATKHAU") String matkhau,
               @WebParam(name = "TRANGTHAI") short trangthai) {
          int kq=0;
           String query="insert into SINHVIEN (MASV,HOTEN,GIOITINH,NGAYSINH,DIACHI,EMAIL,PHONE,MATKHAU,TRANGTHAI)"
                   + "values("+masv+",N'"+hoten+"',"+gioitinh+","+ngaysinh+",N'"+diachi+"','"+email+"','"+phone+"','"+matkhau+"',"+trangthai+");";          
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
       @WebMethod(operationName = "XoaSinhVien")
    public int XoaSinhVien(@WebParam(name = "MASV") String masv) {
           
          int kq=0;
           String query="delete  SINHVIEN where MASV='"+masv+"'";
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
     
       @WebMethod(operationName = "CapNhatSinhVien")
    public int CapNhatSinhVien(@WebParam(name = "MASV") String masv,@WebParam(name = "HOTEN") String hoten,
    @WebParam(name = "GIOITINH") short gioitinh,@WebParam(name = "NGAYSINH") String ngaysinh,
    @WebParam(name = "DIACHI") String diachi,@WebParam(name = "EMAIL") String email,
    @WebParam(name = "PHONE") String phone, @WebParam(name = "TRANGTHAI") short trangthai) {
          int kq=0;
           String query="update SINHVIEN set HOTEN=N'"+hoten+"', GIOITINH="+gioitinh+
                   ",NGAYSINH=' "+ngaysinh+" ', DIACHI=N'"+diachi+" ',EMAIL=' "+email+
                   " ',PHONE=' "+phone+" ',TRANGTHAI="+trangthai+" where MASV='"+masv+"';";                   
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
       @WebMethod(operationName = "DoiMatKhauSinhVien")
    public int DoiMatKhauSinhVien(@WebParam(name = "MASV") String masv,@WebParam(name = "MATKHAUCU") String matkhaucu
               ,@WebParam(name = "MATKHAU") String matkhau) {
          int kq=0;
           String query="update SINHVIEN set MATKHAU="+matkhau+" where MASV='"+masv+"' and MATKHAU="+matkhaucu+";";                   
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
       
        @WebMethod(operationName = "ThemSinhVienTest")
       public int ThemSinhVienTest(@WebParam(name = "MASV") String masv,@WebParam(name = "HOTEN") String hoten  ) 
       {
          int kq=0;
           String query="insert into SINHVIEN (MASV,HOTEN)"
                   + "values("+masv+",'"+hoten+"');";         
      
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
       @WebMethod(operationName = "LayDanhSachSinhVien")
       public LinkedList<sinhvien> LayDanhSachSinhVien(@WebParam(name = "ID") String id)
       {
           String query="";
           int idsv=0;
           try
           {
               idsv=Integer.valueOf(id);
           }
           catch(Exception e)
                   {
                       
                   }
           if(idsv<0) 
           {
               query="select * from SINHVIEN";
           }
           else
            {
               query="select * from SINHVIEN where ID='"+id+"'";
           }
           //connect.createConn();
           ResultSet kq=connect.Select_GetDataDB(query);
           
           LinkedList<sinhvien> a=new LinkedList<sinhvien>();
           try
           {
               while(kq.next())
               {
                    sinhvien temp=new sinhvien();
                    temp.setDiachi(kq.getNString("DIACHI"));
                    temp.setEmail(kq.getString("EMAIL"));
                    temp.setGioitinh(kq.getShort("GIOITINH"));
                    temp.setHoten(kq.getNString("HOTEN"));
                    temp.setMasv(kq.getString("MASV"));
                    temp.setNgaysinh(kq.getString("NGAYSINH"));
                    temp.setPhone(kq.getString("PHONE"));
                    temp.setTrangthai(kq.getShort("TRANGTHAI"));                   
                    //temp.setMatkhau(query);
                    a.add(temp);
                    
               }
           }
           catch(Exception e)
           {
               
           }
           return  a;
       }
       
       @WebMethod(operationName = "LayMotSinhVien")
       public sinhvien LayMotSinhVien(@WebParam(name = "MASV") String MASV)
       {
           String query="";                  
               query="select * from SINHVIEN where MASV='"+MASV+"'";     
           //connect.createConn();
           ResultSet kq=connect.Select_GetDataDB(query);           
          sinhvien a=new sinhvien();
          sinhvien temp=new sinhvien();
           try
           {
               while(kq.next())
               {
                    
                    temp.setDiachi(kq.getNString("DIACHI"));
                    temp.setEmail(kq.getString("EMAIL"));
                    temp.setGioitinh(kq.getShort("GIOITINH"));
                    temp.setHoten(kq.getNString("HOTEN"));
                    temp.setMasv(kq.getString("MASV"));
                    temp.setNgaysinh(kq.getString("NGAYSINH"));
                    temp.setPhone(kq.getString("PHONE"));
                    temp.setTrangthai(kq.getShort("TRANGTHAI"));                   
                    temp.setMatkhau(kq.getString("MATKHAU"));
                    //temp.setMatkhau(query);
                   break;
                    
               }
           }
           catch(Exception e)
           {
               
           }
           return  temp;
       }
       @WebMethod(operationName = "LayDanhSachLopHocCuaSinhVienDK")
         public List<thongtindk>  LayDanhSachLopHocCuaSinhVienDK(@WebParam(name = "MASV") String masv)
         {
             String select="";
            thamso hk=LayThamSo();
                 select="select * from LOPHOC where MALOP="+masv;
            select=" select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU,G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE,M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH "+"  "+
" from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV"+"  "+
 " where L.MALOP in (select L2.MALOP from LOPHOC L2 inner join CHITIET_PDK CT on L2.MALOP=CT.MALOP join PHIEUDANGKY P on P.MAPDK=CT.MAPDK "+
 "  "+"where P.MASV='"+masv+"'  "+" and P.MAHK="+hk.getMahockyhientai()+")";
             ResultSet temp=connect.Select_GetDataDB(select);
              List<thongtindk> ttdk=new LinkedList<thongtindk>();
                 try {
                         while(temp.next())
                         {
                             thongtindk a=new thongtindk();
                            a.getLop().setCa(temp.getShort("CA"));
                            a.getLop().setMaLop(temp.getInt("MALOP"));
                            a.getLop().setMagv(temp.getInt("MAGV"));
                            a.getLop().setMahk(temp.getInt("MAHK"));
                            a.getLop().setMamh(temp.getString("MAMH"));
                            a.getLop().setSoluongdadk(temp.getInt("SOLUONGDADK"));
                            a.getLop().setSoluongdktoda(temp.getInt("SOLUONGDKTOIDA"));
                            a.getLop().setThu(temp.getShort("THU"));
                            
                            a.getGvien().setDiachi(temp.getNString("DIACHI"));
                            a.getGvien().setEmail(temp.getString("EMAIL"));
                            a.getGvien().setGioitinh(temp.getShort("GIOITINH"));
                            a.getGvien().setHocvi_hocham(temp.getString("HOCVI_HOCHAM"));
                            a.getGvien().setHotengv(temp.getNString("HOTENGV"));
                            //a.getGvien().setMagv(temp.getInt("MAGV"));
                            a.getGvien().setNgaysinh(temp.getString("NGAYSINH"));
                            a.getGvien().setPhone(temp.getString("PHONE"));
                            
                           // a.getMon().setMamh(temp.getNString("MAMH"));
                            a.getMon().setTongsotc(temp.getShort("SOTC"));
                            a.getMon().setTclythuyet(temp.getShort("TCLYTHUYET"));
                            a.getMon().setTcthuchanh(temp.getShort("TCTHUCHANH"));
                            a.getMon().setTenmh(temp.getNString("TENMH"));
                            
                            ttdk.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return ttdk;
         }
       @WebMethod(operationName = "TestLayDanhSachLopHocCuaSinhVienDK")
         public thongtindk  TestLayDanhSachLopHocCuaSinhVienDK(@WebParam(name = "MASV") String masv)
         {
             String select="";
            thamso hk=LayThamSo();
                 select="select * from LOPHOC where MALOP="+masv;
            select=" select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU,G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE,M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH "+"  "+
" from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV"+"  "+
 " where L.MALOP in (select L2.MALOP from LOPHOC L2 inner join CHITIET_PDK CT on L2.MALOP=CT.MALOP join PHIEUDANGKY P on P.MAPDK=CT.MAPDK "+
 "  "+"where P.MASV='"+masv+"'  "+" and P.MAHK="+hk.getMahockyhientai()+")";
             ResultSet temp=connect.Select_GetDataDB(select);
              thongtindk a=new thongtindk();
              
                 try {
                         while(temp.next())
                         {
                             
                            a.getLop().setCa(temp.getShort("CA"));
                            a.getLop().setMaLop(temp.getInt("MALOP"));
                            a.getLop().setMagv(temp.getInt("MAGV"));
                            a.getLop().setMahk(temp.getInt("MAHK"));
                            a.getLop().setMamh(temp.getString("MAMH"));
                            a.getLop().setSoluongdadk(temp.getInt("SOLUONGDADK"));
                            a.getLop().setSoluongdktoda(temp.getInt("SOLUONGDKTOIDA"));
                            a.getLop().setThu(temp.getShort("THU"));
                            
                            a.getGvien().setDiachi(temp.getNString("DIACHI"));
                            a.getGvien().setEmail(temp.getString("EMAIL"));
                            a.getGvien().setGioitinh(temp.getShort("GIOITINH"));
                            a.getGvien().setHocvi_hocham(temp.getString("HOCVI_HOCHAM"));
                            a.getGvien().setHotengv(temp.getNString("HOTENGV"));
                            //a.getGvien().setMagv(temp.getInt("MAGV"));
                            a.getGvien().setNgaysinh(temp.getString("NGAYSINH"));
                            a.getGvien().setPhone(temp.getString("PHONE"));
                            
                           // a.getMon().setMamh(temp.getNString("MAMH"));
                            a.getMon().setTongsotc(temp.getShort("SOTC"));
                            a.getMon().setTclythuyet(temp.getShort("TCLYTHUYET"));
                            a.getMon().setTcthuchanh(temp.getShort("TCTHUCHANH"));
                            a.getMon().setTenmh(temp.getNString("TENMH"));
                            
                            break;
                         }
                     } catch (Exception e) {
                     }
                 return a;
         }
       @WebMethod(operationName = "KiemTraPhieuDKSinhVien")
       public int KiemTraPhieuDKSinhVien(@WebParam(name = "MASV") String MASV)
       {
           thamso hk=LayThamSo();
           //String query="select COUNT(P.MAPDK) as NUMBER  from PHIEUDANGKY P  where P.MASV='"+MASV+"' and P.MAHK="+hk.getMahockyhientai();
           String query="select P.MAPDK as NUMBER  from PHIEUDANGKY P  where P.MASV='"+MASV+"' and P.MAHK="+hk.getMahockyhientai();
           ResultSet temp=connect.Select_GetDataDB(query);
           int number=0;
           try
           {                        
               while(temp.next())
               {
                    number=temp.getInt("NUMBER");
                    break;
               }
              
           }
           catch(Exception e)
           {
               return number;
           }
           return number;
       }
       // kết thúc sinh viên.
       
       
     // phần môn học
        @WebMethod(operationName = "ThemMonHoc")
    public int ThemMonHoc(@WebParam(name = "MaMH") String mamh,@WebParam(name = "TENMH") String tenmh,@WebParam(name = "SOTC") short sotc,
    @WebParam(name = "TCLYTHUYET") short tclythuyet,@WebParam(name = "TCTHUCHANH") short tcthuchanh) {
          int kq=0;
           String query="insert into MONHOC (MAMH,TENMH,SOTC,TCLYTHUYET,TCTHUCHANH)"
                   + "values('"+mamh + "',N'"+ tenmh +"',"+sotc+","+tclythuyet+","+tcthuchanh+");";          
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
         @WebMethod(operationName = "SuaMonHoc")
    public int SuaMonHoc(@WebParam(name = "MAMH") String mamh,@WebParam(name = "TENMH") String tenmh,@WebParam(name = "SOTC") short sotc,
    @WebParam(name = "TCLYTHUYET") short tclythuyet,@WebParam(name = "TCTHUCHANH") short tcthuchanh) {
          int kq=0;
           String query="update MONHOC set TENMH=N'"+tenmh+" ' ,SOTC="+sotc+",TCLYTHUYET="+tclythuyet+",TCTHUCHANH="+tcthuchanh+"  "+" where MAMH='"+mamh+"';";                   
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
         @WebMethod(operationName = "XoaMonHoc")
    public int XoaMonHoc(@WebParam(name = "MAMH") String mamh) {
          int kq=0;
           String query="delete MONHOC where MAMH='"+mamh+"'";                   
           kq=connect.Insert_Update_Delete_DataDB(query);           
        return kq;
    }
         @WebMethod(operationName = "LayDanhSachMonHoc")
         public List<monhoc>  LayDanhSachMonHoc(@WebParam(name = "MAMH") String mamh)
         {
             String select="";
             if(mamh.length() == 0)
             {
                 select="select * from MONHOC ";
             }
             else 
             {
                 select="select * from MONHOC where MAMH= '"+ mamh + "'";
             }
             ResultSet temp=connect.Select_GetDataDB(select);
             List<monhoc> mh=new LinkedList<monhoc>();
             try {
                 while(temp.next())
                 {
                     monhoc a=new monhoc();
                     a.setMamh(temp.getString("MAMH"));
                     a.setTenmh(temp.getNString("TENMH"));
                     a.setTongsotc(temp.getShort("SOTC"));
                     a.setTclythuyet(temp.getShort("TCLYTHUYET"));
                     a.setTcthuchanh(temp.getShort("TCTHUCHANH"));
                     mh.add(a);
                 }
             } catch (Exception e) {
                 e.printStackTrace();
             }
             return mh;
         }
       // kết thúc phần môn học
        
         //phần học kỳ
         @WebMethod(operationName = "ThemHocKy")
         public int ThemHocKy(@WebParam(name = "MANAMHOC") int manamhoc,@WebParam(name = "TENHK") String tenhk,
                 @WebParam(name = "THOIGIANBATDAUDK") String thoigianbatdaudk,@WebParam(name = "THOIGIANKETTHUCDK") String thoigianketthucdk)
         {
             String query="insert into HOCKY (MANAMHOC,TENHK,THOIGIANBATDAUDK,THOIGIANKETTHUCDK) "
                     + "values ("+manamhoc+",'"+tenhk+"','"+thoigianbatdaudk+"','"+thoigianketthucdk+"')";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "XoaHocKy")
          public int XoaHocKy(@WebParam(name = "MAHK") int mahk)
         {
             String query="delete from HOCKY where MAHK="+mahk;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "SuaHocKy")
          public int SuaHocKy(@WebParam(name = "MAHK") int mahk,@WebParam(name = "MANAMHOC") int manamhoc,@WebParam(name = "TENHK") String tenhk,
                 @WebParam(name = "THOIGIANBATDAUDK") String thoigianbatdaudk,@WebParam(name = "THOIGIANKETTHUCDK") String thoigianketthucdk)
         {
             String query="update HOCKY set MANAMHOC="+manamhoc+",TENHK='"+tenhk
                     +"',THOIGIANBATDAUDK='"+thoigianbatdaudk+"',THOIGIANKETTHUCDK='"+thoigianketthucdk+"' "+"where MAHK="+mahk;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "LayDanhSachHocKy")
         public List<hocky>  LayDanhSachHocKy(@WebParam(name = "MAHK") int mahk)
         {
             String select="";
             if(mahk<0)
             {
                 select="select * from HOCKY ";
             }
             else 
             {
                 select="select * from HOCKY where MAHK="+mahk;
             }
             ResultSet temp=connect.Select_GetDataDB(select);
             List<hocky> hk=new LinkedList<hocky>();
             try {
                 while(temp.next())
                 {
                     try{
                     hocky a=new hocky();
                     a.setMahk(temp.getInt("MAHK"));
                     a.setManamhoc(temp.getInt("MANAMHOC"));
                     a.setTenhk(temp.getString("TENHK"));
                     a.setThoigianbatdaudk(temp.getString("THOIGIANBATDAUDK"));
                     a.setThoigianketthucdk(temp.getString("THOIGIANKETTHUCDK"));
                     hk.add(a);
                     }
                     catch (Exception e)
                     {
                         e.printStackTrace();
                     }
                 }
             } catch (Exception e) {
                    e.printStackTrace();
             }
             return hk;
         }
         // kết thúc phần học kỳ
          
          //phần lớp học
         @WebMethod(operationName = "ThemLopHoc")
          public int ThemLopHoc(@WebParam(name = "MAMONHOC") String mamh,@WebParam(name = "MAGIANGVIEN") int magv,@WebParam(name = "MAHOCKY") int mahk,
                 @WebParam(name = "SOLUONGDKTOIDA") int soluongdktoida,@WebParam(name = "SOLUONGDADK") int soluongdadk
                  , @WebParam(name = "THU") int thu,@WebParam(name = "CA") int ca)
         {
             String query="insert into LOPHOC (MAMH,MAGV,MAHK,SOLUONGDKTOIDA,SOLUONGDADK,THU,CA) "
                     + "values ('"+mamh+"',"+magv+","+mahk+","+soluongdktoida+","+soluongdadk+","+thu+","+ca+");";
             return connect.Insert_Update_Delete_DataDB(query);
         }
         @WebMethod(operationName = "XoaLopHoc")
          public int XoaLopHoc(@WebParam(name = "MALOP") int malop)
         {
             String query="delete from LOPHOC where MALOP="+malop;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "SuaLopHoc")
          public int SuaLopHoc(@WebParam(name = "MALOP") int malop,@WebParam(name = "MAMONHOC") String mamh,@WebParam(name = "MAGIANGVIEN") int magv,@WebParam(name = "MAHOCKY") int mahk,
                 @WebParam(name = "SOLUONGDKTOIDA") int soluongdktoida, @WebParam(name = "THU") int thu,@WebParam(name = "CA") int ca)
         {
             String query="update LOPHOC set MAMH='"+mamh+"',MAGV="+magv+",MAHK="+mahk
                     +",SOLUONGDKTOIDA="+soluongdktoida+",THU="+thu+",CA="+ca+" "+"where MALOP="+malop+";";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "LayDanhSachLopHoc")
         public List<lophoc>  LayDanhSachLopHoc(@WebParam(name = "MALOP") int malop)
         {
             String select="";
             if(malop<0)
             {
                 select="select * from LOPHOC ";
             }
             else 
             {
                 select="select * from LOPHOC where MALOP="+malop;
             }
             ResultSet temp=connect.Select_GetDataDB(select);
             List<lophoc> lop=new LinkedList<lophoc>();
             try {
                 while(temp.next())
                 {
                     lophoc a=new lophoc();
                    a.setMaLop(temp.getInt("MALOP"));
                    a.setThu(temp.getShort("THU"));
                    a.setCa(temp.getShort("CA"));
                    a.setMagv(temp.getInt("MAGV"));
                    a.setMamh(temp.getString("MAMH"));
                    a.setMahk(temp.getInt("MAHK"));
                    a.setSoluongdktoda(temp.getInt("SOLUONGDKTOIDA"));
                    a.setSoluongdadk(temp.getInt("SOLUONGDADK"));
                     lop.add(a);
                 }
             } catch (Exception e) {
             }
             return lop;
         }
         
          //kết thúc phần lớp học
         
         //phần MÔN TIÊN QUYẾT
         @WebMethod(operationName = "ThemMonTienQuyet")
          public int ThemMonTienQuyet(@WebParam(name = "MAMH_TQ") String mamon_tq,@WebParam(name = "MAMONHOC") String mamh)
         {
             String query="insert into MONTIENQUYET (MAMH_TQ,MAMH) "
                     + "values ('"+mamon_tq+"','"+mamh+"');";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "XoaMonTienQuyet1")
          public int XoaMonTienQuyet1(@WebParam(name = "MAMH_TQ") int mamon_tq,@WebParam(name = "MAMONHOC") int mamh)
         {
             String query="delete from MONTIENQUYET where MAMH_TQ="+mamon_tq+" and MAMH="+mamh;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "XoaMonTienQuyet2")
         public int XoaMonTienQuyet2(@WebParam(name = "MAMONHOC") String mamh)
         {
             String query="delete from MONTIENQUYET where MAMH='"+mamh + "'";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         @WebMethod(operationName = "SuaMonTienQuyet")
          public int SuaMonTienQuyet(@WebParam(name = "MAMH_TQ_OLD") int mamon_tq_old,@WebParam(name = "MAMH_TQ_NEW") int mamon_tq_new,@WebParam(name = "MAMONHOC") int mamh)
         {
             String query="update MONTIENQUYET set MAMH_TQ="+mamon_tq_new+"  "+" where MAMH="+mamh+"and MAMH_TQ="+mamon_tq_old;                     
             return connect.Insert_Update_Delete_DataDB(query);             
         }
         
          @WebMethod(operationName = "LayDanhSachMonTienQuyet")
         public List<montienquyet>  LayDanhSachMonTienQuyet(@WebParam(name = "MAMH") String mamh)
         {
                String  select="select * from MONTIENQUYET where MAMH='"+mamh + "'";             
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<montienquyet> montq=new LinkedList<montienquyet>();
                 try {
                         while(temp.next())
                         {
                            montienquyet a=new montienquyet();
                            a.setMamh(temp.getString("MAMH"));
                            a.setMamh_tq(temp.getString("MAMH_TQ"));
                            montq.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return montq;
         }
         // kết thúc phần môn tiên quyết
         
         //phần NĂM HỌC
          @WebMethod(operationName = "ThemNamHoc")
          public int ThemNamHoc(@WebParam(name = "NAM1") int nam1,@WebParam(name = "NAM2") int nam2)
         {
             String query="insert into NAMHOC (NAM1,NAM2) "
                     + "values ("+nam1+","+nam2+");";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
          @WebMethod(operationName = "XoaNamHoc")
          public int XoaNamHoc(@WebParam(name = "MANAMHOC") int manamhoc)
         {
             String query="delete from NAMHOC where MANAMHOC="+manamhoc;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
          @WebMethod(operationName = "SuaNamHoc")
           public int SuaNamHoc(@WebParam(name = "MANAMHOC") int manamhoc,@WebParam(name = "NAM1") int nam1,@WebParam(name = "NAM2") int nam2)
         {
             String query="update NAMHOC set NAM1="+nam1+",NAM2="+nam2+" "+" where MANAMHOC="+manamhoc;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
          @WebMethod(operationName = "LayDanhSachNamHoc")
           public List<namhoc>  LayDanhSachNamHoc(@WebParam(name = "MAMH") int manamhoc)
         {          
             String select="";
             if(manamhoc<0)
             {
                 select="select * from NAMHOC";
             }
             else
             {
                select="select * from NAMHOC where MANAMHOC="+manamhoc;             
             }
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<namhoc> nh=new LinkedList<namhoc>();
                 try {
                         while(temp.next())
                         {
                             namhoc a=new namhoc();
                             a.setManamhoc(temp.getInt("MANAMHOC"));
                            a.setNam1(temp.getInt("NAM1"));
                            a.setNam2(temp.getInt("NAM2"));
                            nh.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return nh;
         }
         //kết thúc phần NĂM HỌC
          
          //phần PHIẾU ĐĂNG KÝ
           @WebMethod(operationName = "ThemPhieuDangKy")
          public int ThemPhieuDangKy(@WebParam(name = "MASV") String masv,@WebParam(name = "MAHK") int mahk,@WebParam(name = "NGAYDK") String ngaydk)
         {
             //thamso ts=LayThamSo();
             //mahk=ts.getMahockyhientai();             
             String query="insert into PHIEUDANGKY  (MASV,MAHK,NGAYDK) "
                     + "values ('"+masv+"',"+mahk+","+ngaydk+");";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "XoaPhieuDangKy")
          public int XoaPhieuDangKy(@WebParam(name = "MAPDK") int mapdk)
         {
             String query="delete from PHIEUDANGKY  where MAPDK="+mapdk;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "SuaPhieuDangKy")
          public int SuaPhieuDangKy(@WebParam(name = "MAPDK") int mapdk,@WebParam(name = "MASV") int masv,@WebParam(name = "MAHK") int mahk,@WebParam(name = "NGAYDK") String ngaydk)
         {
             String query="update PHIEUDANGKY  set  MASV="+masv+",MAHK="+mahk+",NGAYDK="+ngaydk+" "+" where MAPDK="+mapdk;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "LayDanhSachPhieuDangKy")
           public List<phieudangky>  LayDanhSachPhieuDangKy(@WebParam(name = "MAPDK") int mapdk)
         {          
             String select="";
             if(mapdk<0)
             {
                 select="select * from PHIEUDANGKY";
             }
             else
             {
                select="select * from PHIEUDANGKY where MAPDK="+mapdk;             
             }
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<phieudangky> pdk=new LinkedList<phieudangky>();
                 try {
                         while(temp.next())
                         {
                             phieudangky a=new phieudangky();
                            a.setMahk(temp.getInt("MAHK"));
                            a.setMapdk(temp.getInt("MAPDK"));
                            a.setMasv(temp.getString("MASV"));
                            a.setNgaydk(temp.getString("NGAYDK"));
                            pdk.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return pdk;
         }
          //kết thúc phần PHIẾU ĐĂNG KÝ
           
           //phần chi tiết phiếu đăng ký
           @WebMethod(operationName = "ThemChiTietPhieuDangKy")
          public int ThemChiTietPhieuDangKy(@WebParam(name = "MAPDK") int mapdk,@WebParam(name = "MALOP") int malop)
         {
             //thamso ts=LayThamSo();
             //mahk=ts.getMahockyhientai();             
             String query="insert into CHITIET_PDK  (MAPDK,MALOP) "
                     + "values ("+mapdk+","+malop+");";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           
          @WebMethod(operationName = "LayDSChiTietPhieuDangKy")
          public List<Integer> LayDSChiTietPhieuDangKy(@WebParam(name = "MALOP") int malop)
            {
                 //thamso ts=LayThamSo();
                 //mahk=ts.getMahockyhientai();             
                 String query;
                 if (malop < 0)
                     query = "select * from CHITIET_PDK";

                 else
                     query = "select * from CHITIET_PDK where MALOP = " + malop;
                 ResultSet temp=connect.Select_GetDataDB(query);
                     List<Integer> dspdk=new LinkedList<Integer>();
                     try {
                             while(temp.next())
                               dspdk.add(temp.getInt("MAPDK"));
                         } catch (Exception e) {
                         }
                     return dspdk;
            }
           
           @WebMethod(operationName = "CapNhatSoLuongDaDK")
          public int CapNhatSoLuongDaDK()
         {
             thamso ts=LayThamSo();
             int mahk=ts.getMahockyhientai();             
             String query="UPDATE LOPHOC    SET SOLUONGDADK =SOLUONG     FROM LOPHOC L,(select CT.MALOP, count (CT.MALOP)as SOLUONG from LOPHOC L,CHITIET_PDK CT where L.MALOP=CT.MALOP and L.MAHK="+mahk+"  "+" group by CT.MALOP) L2    where L.MALOP=L2.MALOP";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
            @WebMethod(operationName = "XoaChiTietPhieuDangKy")
          public int XoaChiTietPhieuDangKy(@WebParam(name = "MAPDK") int mapdk)
         {
             //thamso ts=LayThamSo();
             //mahk=ts.getMahockyhientai();             
             String query="delete CHITIET_PDK  where MAPDK="+mapdk;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           //kết thúc phần chi tiết phiếu đăng ký
           
           
           //phần PHÒNG
          @WebMethod(operationName = "ThemPhong")
          public int ThemPhong(@WebParam(name = "SOPHONG") int sophong, @WebParam(name = "SUCCHUA") int succhua,@WebParam(name = "TANG") int tang)
         {
             String query="insert into PHONG "
                     + "values ("+sophong+","+succhua+","+tang+");";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "XoaPhong")
          public int XoaPhong(@WebParam(name = "SOPHONG") int sophong)
         {
             String query="delete PHONG  where SOPHONG="+sophong;                     
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "SuaPhong")
          public int SuaPhong(@WebParam(name = "SOPHONG") int sophong,@WebParam(name = "SUCCHUA") int succhua,@WebParam(name = "TANG") int tang)
         {
             String query="update PHONG set SUCCHUA="+succhua+",TANG="+tang+"  "+" where SOPHONG="+sophong;                     
             return connect.Insert_Update_Delete_DataDB(query);             
         }       
           
            @WebMethod(operationName = "LayDanhSachPhong")
           public List<phong>  LayDanhSachPhong(@WebParam(name = "SOPHONG") int sophong)
         {          
             String select="";
             if(sophong<0)
             {
                 select="select * from PHONG";
             }
             else
             {
                select="select * from PHONG where SOPHONG="+sophong;             
             }
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<phong> p=new LinkedList<phong>();
                 try {
                         while(temp.next())
                         {
                             phong a=new phong();
                            a.setSophong(temp.getInt("SOPHONG"));
                            a.setSucchua(temp.getInt("SUCCHUA"));
                            a.setTang(temp.getInt("TANG"));                          
                            p.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return p;
         }
            
            @WebMethod(operationName = "LayDanhSachPhongTrong")
           public List<phong>  LayDanhSachPhongTrong(@WebParam(name = "THU") int thu, @WebParam(name = "CA") int ca, @WebParam(name = "HOCKY") int mahk)
         {          
             String select="";
                select="select * from PHONG where SOPHONG NOT IN (select SOPHONG from CT_PHONG,LOPHOC where CT_PHONG.MaLop = LOPHOC.MaLop and LOPHOC.thu=" + thu +" and LOPHOC.ca = " + ca + " and LOPHOC.Mahk = " + mahk +")";             
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<phong> p=new LinkedList<phong>();
                 try {
                         while(temp.next())
                         {
                             phong a=new phong();
                            a.setSophong(temp.getInt("SOPHONG"));
                            a.setSucchua(temp.getInt("SUCCHUA"));
                            a.setTang(temp.getInt("TANG"));                          
                            p.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return p;
         }
            
             
            
              @WebMethod(operationName = "LaySoPhong")
           public int LaySoPhong(@WebParam(name = "MALOP") int malop)
         {          
             int result = -1;
             String select="";
                select="select SOPHONG from CT_PHONG where MALOP = " + malop;
                 ResultSet temp=connect.Select_GetDataDB(select);
                 try {
                         while(temp.next())
                             result = temp.getInt("SOPHONG");
                     } catch (Exception e) {
                         e.printStackTrace();
                     }
                 return result;
         }
           // kết thúc phần PHÒNG
           //phần QUẢN TRỊ
           @WebMethod(operationName = "ThemQuanTri")
          public int ThemQuanTri(@WebParam(name = "TENDANGNHAP") String tendangnhap,@WebParam(name = "EMAIL") String email,@WebParam(name = "MATKHAU") String matkhau)
         {
             String query="insert into QUANTRI  (TENDANGNHAP,EMAIL,MATKHAU) "
                     + "values ('"+tendangnhap+" ',' "+email+" ',' "+matkhau+" ');";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "XoaQuanTri")
          public int XoaQuanTri(@WebParam(name = "MANGUOIDUNG") int manguoidung)
         {
             String query="delete QUANTRI  where MANGUOIDUNG="+manguoidung;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "SuaQuanTri")
          public int SuaQuanTri(@WebParam(name = "MANGUOIDUNG") int manguoidung,@WebParam(name = "TENDANGNHAP") String tendangnhap,@WebParam(name = "EMAIL") String email,@WebParam(name = "MATKHAU") String matkhau)
         {
             String query="update QUANTRI set TENDANGNHAP='"+tendangnhap+"',EMAIL='"+email+"',MATKHAU='"+matkhau+"' "+"  "+"where MANGUOIDUNG="+manguoidung;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "LayDanhSachQuanTri")
           public List<quantri>  LayDanhSachQuanTri(@WebParam(name = "MANGUOIDUNG") int manguoidung)
         {          
             String select="";
             if(manguoidung<0)
             {
                 select="select * from QUANTRI";
             }
             else
             {
                select="select * from QUANTRI where MANGUOIDUNG="+manguoidung;             
             }
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<quantri> qt=new LinkedList<quantri>();
                 try {
                         while(temp.next())
                         {
                             quantri a=new quantri();
                           a.setEmail(temp.getString("EMAIL"));
                           a.setManguoidung(temp.getInt("MANGUOIDUNG"));
                           a.setTendangnhap(temp.getString("TENDANGNHAP"));
                           a.setMatkhau(temp.getString("MATKHAU"));
                            qt.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return qt;
         }
           @WebMethod(operationName = "LayMotQuanTri")
           public quantri  LayMotQuanTri(@WebParam(name = "TENDANGNHAP") String tennguoidung)
         {          
             String select="";           
                select="select * from QUANTRI where TENDANGNHAP='"+tennguoidung+"'";           
             
                 ResultSet temp=connect.Select_GetDataDB(select);
                quantri a=new quantri();
                 try {
                         while(temp.next())
                         {
                           a.setEmail(temp.getString("EMAIL"));
                           a.setManguoidung(temp.getInt("MANGUOIDUNG"));
                           a.setTendangnhap(temp.getString("TENDANGNHAP"));
                           a.setMatkhau(temp.getString("MATKHAU"));
                            break;
                         }
                     } catch (Exception e) {
                     }
                 return a;
         }
           // kết thúc QUẢN TRỊ
           //phần CT_PHONG
           
           @WebMethod(operationName = "ThemChiTietPhong")
            public int ThemChiTietPhong(@WebParam(name = "SOPHONG") int sophong,@WebParam(name = "MALOP") int malop,
            @WebParam(name = "MAHK") int mahk,@WebParam(name = "THU") int thu)
         {
             String query="insert into CT_PHONG  (SOPHONG,MALOP,MAHK,THU) "
                     + "values ("+sophong+","+malop+","+mahk+","+thu+");";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
            @WebMethod(operationName = "XoaChiTietPhong")
            public int XoaChiTietPhong(@WebParam(name = "SOPHONG") int sophong,@WebParam(name = "MALOP") int malop)
         {
             String query="delete CT_PHONG  where SOPHONG="+sophong+"  and  MALOP="+malop;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
            @WebMethod(operationName = "SuaChiTietPhong")
            public int SuaChiTietPhong(@WebParam(name = "SOPHONG") int sophong,@WebParam(name = "MALOP") int malop,
            @WebParam(name = "MAHK") int mahk,@WebParam(name = "THU") int thu)
         {
             String query="update CT_PHONG set MAHK="+mahk+",THU="+thu+"   "+"where SOPHONG="+sophong+"  and MALOP="+malop;
                     
             return connect.Insert_Update_Delete_DataDB(query);             
         }
             @WebMethod(operationName = "LayChiTietPhong")
            public chitiet_phong LayChiTietPhong(@WebParam(name = "SOPHONG") int sophong,@WebParam(name = "MALOP") int malop)
         {
             String select="select * from CT_PHONG where SOPHONG="+sophong+"  and  MALOP="+malop;
                     ResultSet temp=connect.Select_GetDataDB(select);
                chitiet_phong ctp=new chitiet_phong();
                 try {
                         while(temp.next())
                         {                                                      
                           ctp.setMahk(temp.getInt("MAHK"));
                           ctp.setMalop(temp.getInt("MALOP"));
                           ctp.setSophong(temp.getInt("SOPHONG"));
                           ctp.setThu(temp.getInt("THU"));
                           break;
                         }
                     } catch (Exception e) {
                     }
                 return ctp;
            
         }
           //kết thúc phần CT_PHONG
            
           //phần TIN TỨC
            @WebMethod(operationName = "ThemTinTuc")
          public int ThemTinTuc(@WebParam(name = "TUADE") String tuade,@WebParam(name = "MOTANGAN") String motangan,@WebParam(name = "MAQUANTRI") int maquantri,
          @WebParam(name = "NGAYDANG") String ngaydang,@WebParam(name = "NOIDUNG") String noidung)
         {
             String query="insert into TINTUC  (TUADE,MOTANGAN,MAQUANTRI,NGAYDANG,NOIDUNG) "
                     + "values (N'"+tuade+"',N'"+motangan+" ',"+maquantri+" ,"+ngaydang+",N'"+noidung+"');";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "XoaTinTuc")
          public int XoaTinTuc(@WebParam(name = "MATINTUC") int matintuc)
         {
             String query="delete TINTUC  where MATINTUC="+matintuc;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "SuaTinTuc")
          public int SuaTinTuc(@WebParam(name = "MATINTUC") int matintuc, @WebParam(name = "TUADE") String tuade,@WebParam(name = "MOTANGAN") String motangan,
          @WebParam(name = "MAQUANTRI") int maquantri,@WebParam(name = "NOIDUNG") String noidung)
         {
             String query="update TINTUC set TUADE=N'"+tuade+" ',MOTANGAN=N'"+motangan+"',MAQUANTRI="+maquantri+",NOIDUNG=N'"+noidung+"' "+"  "+"where MATINTUC="+matintuc;
             return connect.Insert_Update_Delete_DataDB(query);              
         }
           @WebMethod(operationName = "LayDanhSachTinTuc")
           public List<tintuc>  LayDanhSachTinTuc(@WebParam(name = "MATINTUC") int matintuc)
         {          
             String select="";
             if(matintuc<0)
             {
                 select="select * from TINTUC";
             }
             else
             {
                select="select * from TINTUC  where MATINTUC="+matintuc;             
             }
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<tintuc> tt=new LinkedList<tintuc>();
                 try {
                         while(temp.next())
                         {
                             tintuc a=new tintuc();
                             a.setTuade(temp.getNString("TUADE"));
                             a.setMotangan(temp.getNString("MOTANGAN"));
                           a.setMaquantri(temp.getInt("MAQUANTRI"));
                           a.setMatintuc(temp.getInt("MATINTUC"));
                           a.setNgaydang(temp.getString("NGAYDANG"));
                           a.setNoidung(temp.getNString("NOIDUNG"));
                            tt.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return tt;
         }
           //kết thúc phần TIN TỨC
           
           //phần GIẢNG VIÊN
           @WebMethod(operationName = "ThemGiangVien")
          public int ThemGiangVien(@WebParam(name = "HOTENGIANGVIEN") String hotengv,@WebParam(name = "GIOITINH") short gioitinh,
          @WebParam(name = "NGAYSINH") String ngaysinh,@WebParam(name = "HOCVI_HOCHAM") String hocvihocham,
          @WebParam(name = "DIACHI") String diachi,@WebParam(name = "EMAIL") String email,@WebParam(name = "PHONE") String phone)
         {
             String query="insert into GIANGVIEN  (HOTENGV,GIOITINH,NGAYSINH,HOCVI_HOCHAM,DIACHI,EMAIL,PHONE) "
                     + "values (N'"+hotengv+"' ,"+gioitinh+",'"+ngaysinh+"',' "+hocvihocham+" ',N'"+diachi+" ',' "+email+" ',' "+phone+"');";
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "XoaGiangVien")
          public int XoaGiangVien(@WebParam(name = "MAGV") int magv)
         {
             String query="delete GIANGVIEN  where MAGV="+magv;
             return connect.Insert_Update_Delete_DataDB(query);             
         }
           @WebMethod(operationName = "SuaGiangVien")
          public int SuaGiangVien(@WebParam(name = "MAGV") int magv,@WebParam(name = "HOTENGIANGVIEN") String hotengv,@WebParam(name = "GIOITINH") short gioitinh,
          @WebParam(name = "NGAYSINH") String ngaysinh,@WebParam(name = "HOCVI_HOCHAM") String hocvihocham,
          @WebParam(name = "DIACHI") String diachi,@WebParam(name = "EMAIL") String email,@WebParam(name = "PHONE") String phone)
         {
             String query="update GIANGVIEN set HOTENGV=N'"+hotengv+" ',GIOITINH="+gioitinh+",NGAYSINH='"+ngaysinh+"',HOCVI_HOCHAM='"+hocvihocham+"', DIACHI=N'"+diachi+"',EMAIL='"+email+"', PHONE='"+phone+"'"+" "+"where MAGV="+magv;
             return connect.Insert_Update_Delete_DataDB(query);              
         }
           @WebMethod(operationName = "LayDanhSachGiangVien")
           public List<giangvien>  LayDanhSachGiangVien(@WebParam(name = "MAGIANGVIEN") int magv)
         {          
             String select="";
             if(magv<0)
             {
                 select="select * from GIANGVIEN";
             }
             else
             {
                select="select * from GIANGVIEN  where MAGV="+magv;             
             }
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<giangvien> gv=new LinkedList<giangvien>();
                 try {
                         while(temp.next())
                         {
                           giangvien a=new giangvien();
                           a.setDiachi(temp.getNString("DIACHI"));
                           a.setEmail(temp.getString("EMAIL"));
                           if (temp.getBoolean("GIOITINH"))
                               a.setGioitinh((short)1); //NAM
                           else a.setGioitinh((short)0); //NỮ
                           a.setHocvi_hocham(temp.getString("HOCVI_HOCHAM"));
                           a.setHotengv(temp.getString("HOTENGV"));
                           a.setMagv(temp.getInt("MAGV"));
                           a.setNgaysinh(temp.getString("NGAYSINH"));
                           a.setPhone(temp.getString("PHONE"));                           
                            gv.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return gv;
         }
           // kết thúc phần GIẢNG VIÊN
           
           
           // phần cài đặt HỌC KỲ HIỆN TẠI (MẶC ĐịNH)
           @WebMethod(operationName = "SuaHocKyHienTai")
          public int SuaHocKyHienTai(@WebParam(name = "MAHOCKYHIENTAI_NEW") int mahkht_new)
         {
             String query="update THAMSO set MAHOCKYHIENTAI="+mahkht_new;
             return connect.Insert_Update_Delete_DataDB(query);              
         }
           @WebMethod(operationName = "LayThamSo")
           public thamso  LayThamSo()
         {
             String select="select * from THAMSO ";             
             ResultSet temp=connect.Select_GetDataDB(select);
             thamso ts=new thamso();
             try {
                 while(temp.next())
                 {   
                     ts.setMahockyhientai(temp.getInt("MAHOCKYHIENTAI"));    
                     break;
                 }
             } catch (Exception e) {
             }
             return ts;
         }
           //kết thúc HỌC KỲ HIỆN TẠI
           
           //phần lấy đầy đủ thông tin đăng ký để show cho sinh viên đk
          
            @WebMethod(operationName = "LayThongTinDangKy")
           public List<thongtindk>  LayThongTinDangKy()
         {            
                 thamso ts=new thamso();
                 ts=LayThamSo();
                 
                //String  select="select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU"+
                     //   "G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE"+
                    //    "M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH"+
                   //     " from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV where L.MAHK="+ts.getMahockyhientai();             
                String select="select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU,G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE,M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV where L.MAHK="+ts.getMahockyhientai();
                
                 ResultSet temp=connect.Select_GetDataDB(select);
                 List<thongtindk> ttdk=new LinkedList<thongtindk>();
                 try {
                         while(temp.next())
                         {
                             thongtindk a=new thongtindk();
                            a.getLop().setCa(temp.getShort("CA"));
                            a.getLop().setMaLop(temp.getInt("MALOP"));
                            a.getLop().setMagv(temp.getInt("MAGV"));
                            a.getLop().setMahk(temp.getInt("MAHK"));
                            a.getLop().setMamh(temp.getString("MAMH"));
                            a.getLop().setSoluongdadk(temp.getInt("SOLUONGDADK"));
                            a.getLop().setSoluongdktoda(temp.getInt("SOLUONGDKTOIDA"));
                            a.getLop().setThu(temp.getShort("THU"));
                            
                            a.getGvien().setDiachi(temp.getNString("DIACHI"));
                            a.getGvien().setEmail(temp.getString("EMAIL"));
                            a.getGvien().setGioitinh(temp.getShort("GIOITINH"));
                            a.getGvien().setHocvi_hocham(temp.getString("HOCVI_HOCHAM"));
                            a.getGvien().setHotengv(temp.getNString("HOTENGV"));
                            //a.getGvien().setMagv(temp.getInt("MAGV"));
                            a.getGvien().setNgaysinh(temp.getString("NGAYSINH"));
                            a.getGvien().setPhone(temp.getString("PHONE"));
                            
                           // a.getMon().setMamh(temp.getNString("MAMH"));
                            a.getMon().setTongsotc(temp.getShort("SOTC"));
                            a.getMon().setTclythuyet(temp.getShort("TCLYTHUYET"));
                            a.getMon().setTcthuchanh(temp.getShort("TCTHUCHANH"));
                            a.getMon().setTenmh(temp.getNString("TENMH"));
                            
                            ttdk.add(a);
                         }
                     } catch (Exception e) {
                     }
                 return ttdk;
         }
           
           //kết thúc phần lấy full thông tin đăng ký
            public thongtindk  TestLayThongTinDangKy()
         {            
                 thamso ts=new thamso();
                 ts=LayThamSo();
                 
                //String  select="select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU"+
                     //   "G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE"+
                    //    "M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH"+
                   //     " from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV where L.MAHK="+ts.getMahockyhientai();             
                String select="select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU,G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE,M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV where L.MAHK="+ts.getMahockyhientai();
                
                 ResultSet temp=connect.Select_GetDataDB(select);
                 thongtindk a=new thongtindk();
                 int count=0;
                 try {
                         while(temp.next())
                         {
                             count++;  
                            
                            a.getLop().setCa(temp.getShort("CA"));
                            a.getLop().setMaLop(temp.getInt("MALOP"));
                            a.getLop().setMagv(temp.getInt("MAGV"));
                            a.getLop().setMahk(temp.getInt("MAHK"));
                            a.getLop().setMamh(temp.getString("MAMH"));
                            a.getLop().setSoluongdadk(temp.getInt("SOLUONGDADK"));
                            a.getLop().setSoluongdktoda(temp.getInt("SOLUONGDKTOIDA"));
                            a.getLop().setThu(temp.getShort("THU"));
                            
                            a.getGvien().setDiachi(temp.getNString("DIACHI"));
                            a.getGvien().setEmail(temp.getString("EMAIL"));
                            a.getGvien().setGioitinh(temp.getShort("GIOITINH"));
                            a.getGvien().setHocvi_hocham(temp.getString("HOCVI_HOCHAM"));
                            a.getGvien().setHotengv(temp.getNString("HOTENGV"));
                           // a.getGvien().setMagv(temp.getInt("MAGV"));
                            a.getGvien().setNgaysinh(temp.getString("NGAYSINH"));
                            a.getGvien().setPhone(temp.getString("PHONE"));
                            
                          // a.getMon().setMamh(temp.getNString("MAMH"));
                            a.getMon().setTongsotc(temp.getShort("SOTC"));
                            a.getMon().setTclythuyet(temp.getShort("TCLYTHUYET"));
                            a.getMon().setTcthuchanh(temp.getShort("TCTHUCHANH"));
                            a.getMon().setTenmh(temp.getNString("TENMH"));
                            
                            break;
                         }
                     } catch (Exception e) {
                     }
                 return a;
         }
             @WebMethod(operationName = "LayThongTinNamHocHienTai")
           public thongtinnamhochientai  LayThongTinNamHocHienTai()
         {            
                 thamso ts=new thamso();
                 ts=LayThamSo();
                 
                //String  select="select L.CA,L.MALOP,L.MAGV,L.MAHK,L.MAMH,L.SOLUONGDADK,L.SOLUONGDKTOIDA,L.THU"+
                     //   "G.DIACHI,G.EMAIL,G.GIOITINH,G.HOCVI_HOCHAM,G.HOTENGV,G.NGAYSINH,G.PHONE"+
                    //    "M.SOTC,M.TCLYTHUYET,M.TCTHUCHANH,M.TENMH"+
                   //     " from LOPHOC L  inner join MONHOC M on L.MAMH=M.MAMH join GIANGVIEN G on L.MAGV=G.MAGV where L.MAHK="+ts.getMahockyhientai();             
                String select="select * from HOCKY H,NAMHOC N,THAMSO T where H.MAHK=T.MAHOCKYHIENTAI  and H.MANAMHOC=N.MANAMHOC";
                
                 ResultSet temp=connect.Select_GetDataDB(select);
                thongtinnamhochientai a=new thongtinnamhochientai();
                 try {
                         while(temp.next())
                         {                          
                            a.getHk().setMahk(temp.getInt("MAHK"));
                            a.getHk().setManamhoc(temp.getInt("MANAMHOC"));
                            a.getHk().setTenhk(temp.getString("TENHK"));
                            a.getHk().setThoigianbatdaudk(temp.getString("THOIGIANBATDAUDK"));
                            a.getHk().setThoigianketthucdk(temp.getString("THOIGIANKETTHUCDK"));
                            
                            a.getNh().setNam1(temp.getInt("NAM1"));
                            a.getNh().setNam2(temp.getInt("NAM2"));
                            break;
                         }
                     } catch (Exception e) {
                     }
                 return a;
         }
           
}

 